package com.sws.orderMS;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.sws.orderMS.pojo.Client;
import com.sws.orderMS.pojo.CreditCard;
import com.sws.orderMS.pojo.Manager;
import com.sws.orderMS.pojo.Order;
import com.sws.orderMS.pojo.OrderItem;
import com.sws.orderMS.pojo.PaymentMethod;
import com.sws.orderMS.pojo.Product;
import com.sws.orderMS.pojo.User;
import com.sws.orderMS.tool.PropertyManager;

public class OrderMSDao {

	private Connection conn;
	
	public OrderMSDao() {
		conn = getConnection();	
	}
	
	public User login(String username, String password) {
		String sql = "select id, name, username, password, email, phone, balance, adress, note, is_manager, tbl_user_status_id "
				+ "from tbl_user where tbl_user_status_id <> 3 and username = ? and password = ?";
		
		PreparedStatement stmt= getStatement(sql);
		ResultSet rs = null;
		
		try {
			stmt.setString(1, username);
			stmt.setString(2, password);
			
			rs = stmt.executeQuery();
			
			if (rs.next()) {
				User user = null;
				long id = rs.getLong("id");
				String name = rs.getString("name");
				String email = rs.getString("email");
				String phone = rs.getString("phone");
				double balance = rs.getDouble("balance");
				String address = rs.getString("adress");
				String note = rs.getString("note");
				boolean isManager = "1".equals(rs.getString("is_manager"));
				int statusId = rs.getInt("tbl_user_status_id");
				if (isManager) {
					user = new Manager(id, name, username, password, email, phone, address, note, isManager, statusId);
				} else {
					PaymentMethod wallet = new CreditCard();
					wallet.setBalance(balance);
					user = new Client(id, name, username, password, email, phone, wallet, address, note, statusId);
				}
				return user;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(null, stmt, rs);
		}
		return null;
	}
	
	public void signup(String username, String password, String name, String phone, String address) {
		String sql = "insert into tbl_user(name, username, password, phone, adress) values(?, ?, ?, ?, ?)";
		
		PreparedStatement stmt = getStatement(sql);
		
		try {
			stmt.setString(1, name);
			stmt.setString(2, username);
			stmt.setString(3, password);
			stmt.setString(4, phone);
			stmt.setString(5, address);
			
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(null, stmt, null);
		}
	}
	
	public int updateUser(String column, String value, long userId) {
		String sql = "update tbl_user set " + column + " = ? where id = ?";
		PreparedStatement stmt = getStatement(sql);
		int affectRows = -1;
		try {
			stmt.setString(1, value);
			stmt.setLong(2, userId);
			affectRows = stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(null, stmt, null);
		}
		return affectRows;
	}
	
	public int updateUserBalance(double balance, long userId) {
		String sql = "update tbl_user set balance = ? where id = ?";
		
		PreparedStatement stmt = getStatement(sql);
		int affectRows = -1;
		
		try {
			stmt.setDouble(1, balance);
			stmt.setLong(2, userId);
			affectRows = stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(null, stmt, null);
		}
		return affectRows;
	}
	
	public double getUserBalance(User user) {
		String sql = "select balance from tbl_user where id = ?";
		
		PreparedStatement stmt = getStatement(sql);
		ResultSet rs = null;
		double balance = 0;
		
		try {
			stmt.setLong(1, user.getId());
			rs = stmt.executeQuery();
			if (rs.next()) {
				balance = rs.getDouble("balance"); 
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(null, stmt, rs);
		}
		return balance;
	}
	
	public void insertProductType(String code,String name,String description){//增加商品类型
		
		PreparedStatement  statement = null;
		ResultSet rs = null;
		String sql = "insert into tbl_product_type(code,name,description) values(?,?,?)";
		
		try {
			statement = getStatement(sql);
			statement.setString(1, code);
			statement.setString(2, name);
			statement.setString(3, description);
			
			statement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			clean(null,statement,null);
		}
		
	}

	public int getProductTypeId(String name) {
		String sql ="select id from tbl_product_type where name = ?";
		
		PreparedStatement  statement = getStatement(sql);
		ResultSet rs = null;
		int id = -1;
		
		try {
			statement.setString(1, name);
			rs = statement.executeQuery();
			if (rs.next()) {
				id = rs.getInt("id");
			}
			return id;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return id;
	}
	
	public List<String> getAllProductType(){//列出所有的商品类型
		List<String> productType = new ArrayList<String>();
		String sql = "select distinct name from tbl_product_type ";

		ResultSet rs = null;
		PreparedStatement statement = null;
		
		statement = getStatement(sql);
		try {
			rs = statement.executeQuery();
			while (rs.next()) {
				String type = rs.getString("name");
				productType.add(type);
			}
			return productType;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			clean(null, statement, rs);
		}
		return null;
	}

	public void insertProduct(Product product, int productTypeId){//增加产品
		
		PreparedStatement statement = null;
		ResultSet re = null;
		
		String sql = "insert into tbl_product (code,name,price,qty,description,tbl_product_type_id)values(?,?,?,?,?,?)";
		
		try {
			statement = getStatement(sql);
			
			statement.setString(1, product.getCode());
			statement.setString(2, product.getName());
			statement.setDouble(3, product.getPrice());
			statement.setInt(4, product.getQty());
			statement.setString(5, product.getDescription());
			statement.setInt(6, productTypeId);
			
			statement.executeUpdate();	
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			clean(null,statement,re);
		}
	}

	public void delectProduct(int id){//刪除商品,软删除
		
		PreparedStatement statement = null;
		
		String sql = "update tbl_product set product_status = ? where id = ?";
		
		statement = getStatement(sql);
		try {
			statement.setInt(1, 0);
			statement.setLong(2, id);
			
			statement.execute();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			clean(null,statement,null);
		}
			
	}
	
	public List<String> getProductCollunm() {//获取商品表中的可以改变的字段
		List<String> collumn = new ArrayList<String>();
		PreparedStatement statement = null;
		ResultSet rs = null;
		String sql = "select COLUMN_NAME from information_schema.COLUMNS where table_name = 'tbl_product';";
		
		try {
			statement = getStatement(sql);
			rs =statement.executeQuery();
			
			while(rs.next()){
				String type = rs.getString("COLUMN_NAME");
				collumn.add(type);
			}
			return collumn;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			clean(null,statement,rs);
		}
		return null;
		
	}

	public void updateProduct(Product product){//改变商品的状态
		PreparedStatement statement= null;
		ResultSet rs =null;
		String sql = "update tbl_product set name =?,price=?,qty=?,description=? where id =?";
		
		boolean isUpdate = product.getId() != -1;
		
		try {
			statement =getStatement(sql);
			
			statement.setString(1, product.getName());
			statement.setDouble(2, product.getPrice());
			statement.setInt(3, product.getQty());
			statement.setString(4, product.getDescription());			
			if(isUpdate){
				statement.setLong(5, product.getId());
			}
			statement.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			clean(conn,statement,null);
		}
	}
	
	public Product searchProductByCode(String code) {
		String sql = "select p.id, p.name, p.price, p.qty, p.description, pt.name as 'type' "
				+ "from tbl_product p, tbl_product_type pt "
				+ "where p.tbl_product_type_id = pt.id "
				+ "and p.code = ? "
				+ "and p.product_status <> 0 ";
		
		PreparedStatement stmt = getStatement(sql);
		ResultSet rs = null;
		
		try {
			stmt.setString(1, code);
			rs = stmt.executeQuery();
			if (rs.next()) {
				long id = rs.getLong("id");
				String name = rs.getString("name");
				double price = rs.getDouble("price");
				int qty = rs.getInt("qty");
				String description = rs.getString("description");
				String type = rs.getString("type");
				Product product = new Product(id, code, name, type, price, qty, description);
				return product;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(null, stmt, rs);
		}
		return null;
	}

	public List<Product> searchAllProducts() {
		String sql = "select p.id, p.code, p.name, p.price, p.qty, p.description, pt.name as 'type' "
				+ "from tbl_product p, tbl_product_type pt "
				+ "where p.tbl_product_type_id = pt.id "
				+ "and p.product_status <> 0 ";
		PreparedStatement stmt = getStatement(sql);
		ResultSet rs = null;
		List<Product> products = new ArrayList<Product>();
		
		try {
			rs = stmt.executeQuery();
			while (rs.next()) {
				long id = rs.getLong("id");
				String code = rs.getString("code");
				String name = rs.getString("name");
				double price = rs.getDouble("price");
				int qty = rs.getInt("qty");
				String description = rs.getString("description");
				String type = rs.getString("type");
				Product product = new Product(id, code, name, type, price, qty, description);
				products.add(product);
			}
			return products;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(null, stmt, rs);
		}		
		return null;
	}
	
	public List<Product> searchProductsByName(String name) {
		String sql = "select p.id, p.code, p.price, p.qty, p.description, pt.name as 'type' "
				+ "from tbl_product p, tbl_product_type pt "
				+ "where p.tbl_product_type_id = pt.id "
				+ "and p.name = ? "
				+ "and p.product_status <> 0 ";
		PreparedStatement stmt = getStatement(sql);
		ResultSet rs = null;
		List<Product> products = new ArrayList<Product>();
		
		try {
			stmt.setString(1, name);
			rs = stmt.executeQuery();
			while (rs.next()) {
				long id = rs.getLong("id");
				String code = rs.getString("code");
				double price = rs.getDouble("price");
				int qty = rs.getInt("qty");
				String description = rs.getString("description");
				String type = rs.getString("type");
				Product product = new Product(id, code, name, type, price, qty, description);
				products.add(product);
			}
			return products;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(null, stmt, rs);
		}		
		return null;
	}
	
	public List<Product> searchProductsByType(String type) {
		String sql = "select p.id, p.code, p.name, p.price, p.qty, p.description "
				+ "from tbl_product p, tbl_product_type pt "
				+ "where pt.name = ? "
				+ "and p.tbl_product_type_id = pt.id "
				+ "and p.product_status <> 0 ";
	
		PreparedStatement stmt = getStatement(sql);
		ResultSet rs = null;
		List<Product> products = new ArrayList<Product>();
		
		try {
			stmt.setString(1, type);
			rs = stmt.executeQuery();
			while (rs.next()) {
				long id = rs.getLong("id");
				String code = rs.getString("code");
				String name = rs.getString("name");
				double price = rs.getDouble("price");
				int qty = rs.getInt("qty");
				String description = rs.getString("description");
				Product product = new Product(id, code, name, type, price, qty, description);
				products.add(product);
			}
			return products;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(null, stmt, rs);
		}		
		return null;
	}
	
	public void insertOrder(Order order, User user) {
		PreparedStatement stmt = null;
		PreparedStatement stmt2 = null;
		
		try {
//			conn.setAutoCommit(false);
			
			String sql = "insert into tbl_order (order_no, create_time, memo, total, tbl_order_status_id, tbl_user_id) values(?, ?, ?, ?, ?, ?)";
			
			String sql2 = "insert into tbl_order_item (item_no, amount, memo, tbl_product_code, order_No) values(?, ?, ?, ?, ?)";
			
			stmt = getStatement(sql);
			stmt2 = getStatement(sql2);
			
			stmt.setString(1, order.getOrderNo());
			stmt.setTimestamp(2, order.getTimeStamp());
			stmt.setString(3, order.getMemo());
			stmt.setDouble(4, order.getTotal());
			stmt.setInt(5, order.getCurrentStatus());
			stmt.setLong(6, user.getId());
			stmt.executeUpdate();
			
			for (OrderItem item: order.getOrderList()) {
				stmt2.setString(1, item.getItemNo());
				stmt2.setInt(2, item.getAmount());
				stmt2.setString(3, item.getMemo());
				stmt2.setString(4, item.getProduct().getCode());
				stmt2.setString(5, order.getOrderNo());
//				stmt2.executeUpdate();
				stmt2.addBatch();
			}
			stmt2.executeBatch();
//			conn.commit();
		} catch (SQLException e) {
//			try {
//				conn.rollback();
//			} catch (SQLException e1) {
//				e1.printStackTrace();
//			}
			e.printStackTrace();
		} finally {
//			try {
//				conn.setAutoCommit(true);
//			} catch (SQLException e) {
//				e.printStackTrace();
//			}
			clean(null, stmt, null);
			clean(null, stmt2, null);
		}
	}
	
	public void insertPayment(Order order) {
		String sql = "insert into tbl_payment (tbl_payment_status_id, tbl_payment_method_id, order_no) values(?, ?, ?)";
		
		PreparedStatement stmt = getStatement(sql);
		
		try {
			stmt.setInt(1, order.getCurrentPaymentStatus());
			stmt.setInt(2, order.getPaymentMethodId());
			stmt.setString(3, order.getOrderNo());
			
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(null, stmt, null);
		}
	}
	
	public void updateOrderStatuss(int orderStatus, Order order) {
		String sql = "update tbl_order set tbl_order_status_id = ? where order_no = ? ";
		
		PreparedStatement stmt = getStatement(sql);
		
		try {
			stmt.setInt(1, orderStatus);
			stmt.setString(2, order.getOrderNo());
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(null, stmt, null);
		}
	}
	
	public void updatePayment(Order order) {
		String sql = "update tbl_payment set tbl_payment_status_id = ? where order_no = ?";
		
		PreparedStatement stmt = getStatement(sql);
		
		try {
			stmt.setInt(1, order.getCurrentPaymentStatus());
			stmt.setString(2, order.getOrderNo());
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(null, stmt, null);
		}
	}
	
	public void updateInventory(Order order) {
		String sql = "update tbl_product set qty = qty - ? where code =  ?";
		
		PreparedStatement stmt = getStatement(sql);
		
		try {
			for (OrderItem item: order.getOrderList()) {
				stmt.setInt(1, item.getAmount());
				stmt.setString(2, item.getProduct().getCode());
				stmt.addBatch();
			}
			stmt.executeBatch();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(null, stmt, null);
		}	
	}
	
	public List<Order> searchAllOrders(User user) {
		String sql = "select t.id, t.order_no, t.create_time, t.memo, t.total, t.tbl_order_status_id, p.tbl_payment_status_id, p.tbl_payment_method_id "
				+ "from tbl_order t, tbl_payment p "
				+ "where t.tbl_user_id = ? "
				+ "and p.order_no = t.order_no "
				+ "and t.tbl_order_status_id <> 3";
		
		PreparedStatement stmt = getStatement(sql);
		ResultSet rs = null;
		List<Order> orderList = new ArrayList<>();
		try {
			stmt.setLong(1, user.getId());
			rs = stmt.executeQuery();
			
			while (rs.next()) {
				long id = rs.getLong("id");
				String orderNo = rs.getString("order_no");
				List<OrderItem> itemList = searchOrderItemBy(orderNo);
				Timestamp timeStamp = rs.getTimestamp("create_time");
				String memo = rs.getString("memo");
				double total = rs.getDouble("total");
				int orderStatusId = rs.getInt("tbl_order_status_id");
				int paymentStatus = rs.getInt("tbl_payment_status_id");
				int paymentMethodId = rs.getInt("tbl_payment_method_id");
				Order order = new Order(id, orderNo, itemList, timeStamp, 
						memo, total, orderStatusId,
						paymentStatus, paymentMethodId);
				orderList.add(order);
			}
			return orderList;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(null, stmt, rs);
		}
		return null;
	}
	
	public List<OrderItem> searchOrderItemBy(String orderNo) {
		String sql = "select t.id, t.item_no, t.amount, t.memo, t.tbl_product_code "
				+ "from tbl_order_item t where t.order_No = ?";
		
		PreparedStatement stmt = getStatement(sql);
		ResultSet rs = null;
		List<OrderItem> itemList = new ArrayList<>();
		
		try {
			stmt.setString(1, orderNo);
			rs = stmt.executeQuery();
			
			while (rs.next()) {
				long id = rs.getLong("id");
				String itemNo = rs.getString("item_no");
				int amount = rs.getInt("amount");
				String memo = rs.getString("memo");
				String productCode = rs.getString("tbl_product_code");
				Product product = searchProductByCode(productCode);
				OrderItem orderItem = new OrderItem(id, itemNo, product, amount, memo);
				itemList.add(orderItem);
			}
			return itemList;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(null, stmt, rs);
		}
		return null;
	}
	
	public boolean isUsernameExists(String username) {
		String sql = "select username from tbl_user where username = ?";
		
		PreparedStatement stmt = getStatement(sql);
		ResultSet rs = null;
		
		try {
			stmt.setString(1, username);
			rs = stmt.executeQuery();
			if (rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(null, stmt, rs);
		}
		return false;
	}
	
	private PreparedStatement getStatement(String sql) {
		PreparedStatement stmt = null;
		try {		
			stmt = conn.prepareStatement(sql);
			return stmt;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	private Connection getConnection() {
		Connection conn = null;
		String url = getProperty("url");
		String user = getProperty("user");
		String password = getProperty("password");
		try {
			conn = DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	private String getProperty(String key) {
		PropertyManager pm = PropertyManager.getInstance("conn.properties");
		return pm.getProperty(key);
	}
	
	public void clean(Connection conn, Statement stmt, ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
}
